package com.esdk.utils;

import cn.hutool.core.lang.Snowflake;
import com.esdk.esdk;
import com.esdk.sql.JDBCTemplate;
import com.esdk.sql.SQLAssistant;
import com.esdk.sql.SQLRuntimeException;
import com.esdk.sql.orm.*;

import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Date;
import java.util.*;

public class EasySql{

  public static final Integer INIT_SEQ_VAL=esdk.prop.getInteger("initSeqVal",10*1000*1000);

  public static List<List> toLists(ResultSet rs) throws SQLException{
    ResultSetMetaData rsmd=rs.getMetaData();
    int FieldsCount=rsmd.getColumnCount();
    ArrayList result=new ArrayList();
    while(rs.next()){
      ArrayList subList=new ArrayList(FieldsCount);
      for(int i=0;i<FieldsCount;i++){
        subList.add(rs.getObject((i+1)));
      }
      result.add(subList);
    }
    rs.close();
    return result;
  }

  public static List<Object> toList(ResultSet rs,String columnName) throws SQLException{
    ArrayList result=new ArrayList();
    while(rs.next()){
      result.add(rs.getObject(columnName));
    }
    rs.close();
    return result;
  }

  public static List<Object[]> toList(ResultSet rs,String... columns) throws SQLException{
    ArrayList result=new ArrayList();
    while(rs.next()){
      Object[] row=new Object[columns.length];
      for(int i=0;i<columns.length;i++){
        row[i]=rs.getObject(columns[i]);
      }
      result.add(row);
    }
    rs.close();
    return result;
  }

  public static Object[][] rsToArr2(ResultSet rs) throws SQLException{
    return rsToArr2(rs);
  }

  public static Object[][] rsToArr2(ResultSet rs,String... columns) throws SQLException{
    ResultSetMetaData rsmd=rs.getMetaData();
    String[] colums=columns;
    int columnCount=colums.length>0?colums.length:rsmd.getColumnCount();
    if(colums.length==0){
      colums=new String[columnCount];
      for(int i=0;i<columnCount;i++){
        colums[i]=rsmd.getColumnLabel(i+1);
      }
    }
    ArrayList<Object[]> list=new ArrayList();
    while(rs.next()){
      Object[] arr=new String[columnCount];
      for(int i=0;i<arr.length;i++){
        if(columns.length==0)
          arr[i]=rs.getObject(i+1);// //下标从1开始,解决中文乱码问题
        else
          arr[i]=rs.getObject(columns[i]);
      }
      list.add(arr);
    }
    if(list.size()==0){
      return new String[0][0];
    }
    Object[][] result=esdk.array.toArray(list);
    return result;
  }

  public static Object[][] rsToArr2WithHeader(ResultSet rs) throws SQLException{
    ResultSetMetaData rsmd=rs.getMetaData();
    int FieldsCount=rsmd.getColumnCount();
    ArrayList list=new ArrayList();
    Object[] arr=new String[FieldsCount];
    for(int i=0;i<FieldsCount;i++){
      arr[i]=rsmd.getColumnLabel(i+1);
    }
    list.add(arr);
    while(rs.next()){
      arr=new String[FieldsCount];
      for(int i=0;i<arr.length;i++){
        arr[i]=rs.getObject(i+1);
      }
      list.add(arr);
    }
    if(list.size()==0){
      return new String[0][0];
    }
    String[][] result=new String[list.size()][FieldsCount];
    for(int i=0;i<list.size();i++){
      System.arraycopy(list.get(i),0,result[i],0,FieldsCount);
    }
    list.clear();
    list=null;
    return result;
  }

  public static Object[][] resultSetToArrWithHeader(ResultSet rs,String... columns) throws SQLException{
    if(columns.length==0)
      return rsToArr2WithHeader(rs);
    ArrayList list=new ArrayList();
    list.add(columns);
    while(rs.next()){
      List record=new ArrayList(columns.length);
      for(String col:columns){
        record.add(rs.getString(col));
      }
      list.add(record);
    }
    return esdk.str.listToArr2(list);
  }


  public static String[] getFieldsNames(Statement stmt,String tablename) throws SQLException{
    String[] result=null;
    int FieldsCount=-1;
//<获取结果集中的列名>
    ResultSet rs=null;
    rs=stmt.executeQuery("select top 1 * from "+tablename);
    ResultSetMetaData rsmd=rs.getMetaData();
    FieldsCount=rsmd.getColumnCount();
    result=new String[FieldsCount];
    for(int i=1;i<FieldsCount+1;i++){ //column indices start from 1
      result[i-1]=rsmd.getColumnLabel(i);
//      tableName=rsmd.getTableName(i); //get the name of the column's table name,but i can't find any avild datas
    }
//</获取结果集中的列名>
    return result;
  }

  public static String[] getFieldNames(ResultSet rs) throws SQLException{
    ResultSetMetaData rsmd=rs.getMetaData();
    int FieldsCount=rsmd.getColumnCount();
    String[] result=new String[FieldsCount];
    for(int i=0;i<FieldsCount;i++){
      result[i]=rsmd.getColumnLabel(i+1);
    }
    return result;
  }

  public static int getFieldsCount(Statement stmt,String sql) throws SQLException{ //得到字段数量
    int result=-1, FieldsCount=-1;
    ResultSet rs=null;
    rs=stmt.executeQuery(sql);
    ResultSetMetaData rsmd=rs.getMetaData();
    FieldsCount=rsmd.getColumnCount();
    result=FieldsCount;
    return result;
  }

  public static BigDecimal getTableID(Connection connection){
    boolean flag=true;
    String id=null;
    do{
      try{
        id=esdk.tool.occurID();
        PreparedStatement st=connection.prepareStatement("insert Register (RegisterID) values("+id.toString()+")");
        st.execute();
        flag=false;
      }catch(SQLException sql){
        if(sql.getErrorCode()!=2627)
          flag=false;
      }
    }
    while(flag);
    return new BigDecimal(id);
  }
  public static void close(Statement st){
    try{
      if(st!=null&&!st.isClosed()){
        st.close();
      }
    }catch(SQLException e){
      esdk.serr("关闭Statement失败，请检查原因："+esdk.tool.getExceptionStackTrace(e));
    }
  }

  /**正常应使用pstmt.closeOnCompletion()，不需要手工关闭*/
  public static void close(ResultSet rs){
    try{
      if(rs!=null){
        Statement stmt=rs.getStatement();
        rs.close();
        if(stmt!=null&&!stmt.isClosed())
          stmt.close();
      }
    }catch(SQLException e){
      esdk.serr("关闭ResultSet失败，请检查原因："+esdk.tool.getExceptionStackTrace(e));
    }
  }

  public static String getClientInfo(Connection conn,String key){
    try{
      return conn.getClientInfo(key);
    }catch(SQLException e){
      if(e.getMessage().contains("connection closed"))
        return "数据库已关闭，无法读取"+key+"内容！";
      else
        throw new SQLRuntimeException(e);
    }
  }

  public static boolean isClosed(Connection conn){
    if(conn==null)
      return true;
    try{
      boolean result=conn.isClosed();
      if(!result)
        conn.setAutoCommit(conn.getAutoCommit());
      return result;
    }catch(SQLException e){
//			e.printStackTrace();
      EasySql.close(conn);
      esdk.serr("无效的数据库连接已被关闭！");
      return true;
    }
  }

  public static void close(Connection conn){
    try{
      if(conn!=null&&!conn.isClosed())
        conn.close();
    }catch(SQLException e){
      throw new SQLRuntimeException(e);
    }
  }

  public static void rollback(java.sql.Connection conn){
    try{
      if(!conn.isClosed()){
        conn.rollback();
      }
    }catch(Exception e){
      throw new SQLRuntimeException(e);
    }
  }

  public static String addSingleQuotesBySqlIn(String value){
    return "'"+value.replaceAll(",","','")+"'";
  }

  public static int getColumnType(String tableName,String columnName,Connection conn) throws SQLException{
    tableName=tableName.toUpperCase();//hqlsdb需要表名大写,否则找不到记录
    ResultSet rs=conn.getMetaData().getColumns(null,null,tableName,columnName);
    int result=0;
    if(rs.next())
      result=rs.getInt("DATA_TYPE");
    return result;
  }

  public static String[] findUniqueColumnNames(String tableName,Connection conn) throws SQLException{
    tableName=tableName.toUpperCase();//hqlsdb需要表名大写,否则找不到记录
    ArrayList result=new ArrayList();
    ResultSet indexRs=conn.getMetaData().getIndexInfo(null,null,tableName,true,false);
/*    try{
      String[][] a=ffy.ffysql.ResultSetToArrWithColumn(indexRs);
      esdk.sout(ffystr.ArrToCsv(a,","));
    }
    catch(Exception e){
      e.printStackTrace();
    }*/

    while(indexRs.next()){
      String columnName=indexRs.getString("COLUMN_NAME");
      if(columnName!=null){
        if(indexRs.getInt("Type")==DatabaseMetaData.tableIndexClustered){//TYPE=1 is PK, TYPE=3 is INDEX
          int datatype=getColumnType(tableName,columnName,conn);
          if(datatype==java.sql.Types.VARCHAR){
            result.add(columnName);
            break;
          }
        }else if(indexRs.getInt("TYPE")==DatabaseMetaData.tableIndexOther){
          boolean isPKColumn=columnName.equalsIgnoreCase(tableName.concat("ID"));
          if(!isPKColumn) //hsqldb的type都是3,不能以type判断,如果是主键值字段,不应该加入到结果中
            result.add(columnName);
        }
      }
    }
    indexRs.close();
    return (String[])result.toArray(new String[0]);
  }

  public static String findPKColumn(String tableName,Connection conn) throws SQLException{
    // tableName=tableName.toUpperCase();// hqlsdb需要表名大写,否则找不到记录
    ResultSet pkRs=conn.getMetaData().getPrimaryKeys(null,null,tableName);
    String columnName=null;
    while(pkRs.next()){//未考虑多主键值的情况,请注意,以后解决
      columnName=pkRs.getString("COLUMN_NAME");
      break;
    }
    pkRs.close();
    return columnName;
  }

  public static int genPrimaryKey(String tableName){
    return genPrimaryKey(tableName,INIT_SEQ_VAL);
  }

  /**
   * 使用redis生成自增ID，用于代替数据库的自增序列的方式
   * ，优点是java端全控制，解决雪花id丢失精度的问题，也能跨JVM保持一致
   * ，缺点是最大只能处理20亿的数据量，不能全局唯一ID，而且id可以被预测
   * ，适用场景：功能比较简单的表，预期三年之内数据量不会超过1千万的表
   *
   * @param tableName  表名，
   * @param initSeqVal id初始值，建议从10,000,000开始
   */
  public static int genPrimaryKey(String tableName,int initSeqVal){
    String sequenceKey="table_"+esdk.str.getText(tableName,"global")+"_seq";
    RedisClient rc=new RedisClient();
    rc.setnx(sequenceKey,initSeqVal);
    int result=(int)rc.incr(sequenceKey);
    rc.close();
    return result;
  }

  public static void resetPrimaryKey(String tableName){
    String sequenceKey="table_"+esdk.str.getText(tableName,"global")+"_seq";
    RedisUtils.set(sequenceKey,INIT_SEQ_VAL);
  }

  private static final SnowFlake snowFlake=SnowFlake.getInstance();

  /**
   * 使用自定义的雪花算法得到全局唯一，注意这是18位的雪花，正常是19位长度的
   */
  public static long genMySnowFlakeId(){
    return snowFlake.nextId();
  }

  /**
   * 标准的雪花算法
   */
  private static final Snowflake hutoolSnowFlake=new Snowflake();

  public static long genSnowFlakeId(){
    return hutoolSnowFlake.nextId();
  }


  /**判断是否为雪花ID*/
  public static boolean isSnowId(Object value) {
    if(value!=null&&value instanceof Long && ((Long)value)>10000000000000000L)
      return true;
    else
      return false;
  }

  public static long genIntId(){
    return IntegerFlake.nextId();
  }

  public static String getCreateTableScript(String tableName,DatabaseMetaData dbmd) throws Exception{
    ResultSet columnRs=dbmd.getColumns(null,"%",tableName,"%");
    ResultSet primaryRs=dbmd.getPrimaryKeys(null,null,tableName);
    ResultSet indexRs=dbmd.getIndexInfo(null,null,tableName,true,false);
    StringBuffer result=new StringBuffer();
    result.append("Create Table ").append(tableName).append("(");
/*    String csv=ffystr.ArrToCsv(ffysql.ResultSetToArrWithColumn(columnRs),",");
    String csv1=ffystr.ArrToCsv(ffysql.ResultSetToArrWithColumn(primaryRs),",");
    String csv2=ffystr.ArrToCsv(ffysql.ResultSetToArrWithColumn(indexRs),",");
    new TString(new String[] {csv,csv1,csv2}).save(new File("c:/dddb.txt"));*/
    HashSet primaryKey=new HashSet();
    while(primaryRs.next()){
      primaryKey.add(primaryRs.getString("COLUMN_NAME"));
    }
    HashMap map=new HashMap();
    while(indexRs.next()){
      if(!indexRs.getBoolean("NON_UNIQUE")&&indexRs.getInt("TYPE")==3){
        String indexname=indexRs.getString("INDEX_NAME");
        if(map.get(indexname)==null){
          map.put(indexname,new CharAppender(','));
        }
        ((CharAppender)map.get(indexname)).append(indexRs.getString("COLUMN_NAME"));
      }
    }
    CharAppender uniqueIndexConstraints=new CharAppender(',');
    for(Iterator iter=map.keySet().iterator();iter.hasNext();){
      String indexname=(String)iter.next();
      uniqueIndexConstraints.append("constraint "+indexname+" unique("+map.get(indexname).toString()+")");
    }
    CharAppender ca=new CharAppender(',');
    for(;columnRs.next();){
      String columnType=columnRs.getString("TYPE_NAME");
      int columnsize=columnRs.getInt("COLUMN_SIZE");
      if(columnsize>100)
        columnsize=18;
      if(columnType.toLowerCase().startsWith("numeric"))
        columnType+="("+columnsize+","+columnRs.getString("DECIMAL_DIGITS")+")";
      else if(columnType.toLowerCase().indexOf("char")>=0)
        columnType+="("+columnsize+")";
      String primaryFlag=(primaryKey.contains(columnRs.getString("COLUMN_NAME"))?" primary key":"");
      String nullableFlag=columnRs.getBoolean("NULLABLE")?"":" not null";
      String defaultvalue=EasyStr.isBlank(columnRs.getString("COLUMN_DEF"))?"":" default "+columnRs.getString("COLUMN_DEF");
      ca.append(columnRs.getString("COLUMN_NAME")+" "+columnType+nullableFlag+defaultvalue+primaryFlag);
    }
    result.append(ca);
    if(uniqueIndexConstraints.length()>0)
      result.append(",").append(uniqueIndexConstraints);
    result.append(")");
    return result.toString();
  }

  public static String[] getTablesAndViews(Connection conn) throws SQLException{
    return getTablesAndViews(conn.getMetaData());
  }

  public static String[] getTablesAndViews(Connection conn,String... key) throws SQLException{
    return getTablesAndViews(conn.getMetaData(),key);
  }

  public static String[] getTablesAndViews(DatabaseMetaData meta) throws SQLException{
    return getTablesAndViews(meta,new String[]{"TABLE","VIEW"});
  }

  public static String[] getTablesAndViews(DatabaseMetaData meta,String... key) throws SQLException{
    ArrayList tableList=new ArrayList();
    String catelog=meta.getConnection().getCatalog();
    ResultSet rs=meta.getTables(catelog,"%","%",key);
    while(rs.next()){
      String tablename=rs.getString("TABLE_NAME");
      tableList.add(tablename);
    }
    return (String[])tableList.toArray(new String[0]);
  }

  /**
   * 如果出现BIN$开头的表,请执行:purge recyclebin;
   */
  public static String[] getOracleTablesAndViews(Connection conn,String category,String schema) throws SQLException{
    ArrayList tableList=new ArrayList();
    ResultSet rs=conn.getMetaData().getTables(category,schema,"%",new String[]{"TABLE","VIEW"});
    while(rs.next()){
      String tablename=rs.getString("TABLE_NAME");
      tableList.add(tablename);
    }
    return (String[])tableList.toArray(new String[0]);
  }

  public static Timestamp getOracleTimestamp(Object value){
    try{
      Class clz=value.getClass();
      Method m=clz.getMethod("timestampValue",(Class[])null);
      // m = clz.getMethod("timeValue", null); 时间类型
      // m = clz.getMethod("dateValue", null); 日期类型
      return (Timestamp)m.invoke(value,(Object[])null);
    }catch(Exception e){
      return null;
    }
  }

  public static String[] getMssql2008TablesAndViews(Connection conn) throws Exception{
    JDBCTemplate jt=new JDBCTemplate(conn,"SELECT name FROM sysobjects WHERE xtype='U'  or xtype = 'V'");
    return jt.toABResultSet().getStrings("name");
  }

  public static String format(String sql,Map<String,Object> params){
    HashMap newParams=new HashMap(params.size());
    for(Iterator iter=params.keySet().iterator();iter.hasNext();){
      String key=(String)iter.next();
      newParams.put(key,SQLAssistant.getDialect().getJDBCTemplateParams(params.get(key)));
    }
    return EasyStr.format(sql,newParams);
  }

  public static String format(String sql,Object... params){
    String[] newParams=new String[params.length];
    for(int i=0;i<params.length;i++){
      newParams[i]=SQLAssistant.getDialect().getJDBCTemplateParams(params[i]);
    }
    return EasyStr.format(sql,newParams);
  }

  /**
   * 如果有*号换成%，否则前后加%符号，并且把空格换成%
   */
  public static String wildcard(String keyword){
    String result=keyword.trim();
    if(keyword.contains("*"))
      result=result.replace("*","%");
    else
      result=("%"+result.replaceAll(" ","%")+"%").replaceAll("%%","%");
    return result.trim();
  }

  public static String[] wildcard(String[] keywords){
    for(int i=0;i<keywords.length;i++){
      keywords[i]=wildcard(keywords[i]);
    }
    return keywords;
  }

  public static int executeBatchUpdate(Connection conn,List<String> multiSql,int partitionSize) throws SQLException{
    return executeBatchUpdate(conn,multiSql,partitionSize,true,false);
  }

  public static int executeBatchUpdate(Connection conn,List<String> multiSql,int partitionSize,boolean allowMultiQueries,boolean isAutoCommit) throws SQLException{
//    TimeMeter tm=new TimeMeter();
    int total=0;
    if(multiSql.size()>partitionSize){
      List<List<String>> partition=esdk.array.partition(multiSql,partitionSize);
      for(List<String> subList: partition){
        int subTotal=executeBatchUpdate(conn,subList,allowMultiQueries,isAutoCommit);
        total+=subTotal;
      }
    }else{
      int subTotal=executeBatchUpdate(conn,multiSql,allowMultiQueries,isAutoCommit);
      total+=subTotal;
    }
//    SQLAssistant.printSql(esdk.str.format("已执行{}条update语句，总耗时{},SQL：{}...",total[0],tm.getHumanElapse(),esdk.str.addEnd(multiSql.get(0),";")));
    return total;
  }

  /**
   * 不分页，一次性执行
   * @param allowMultiQueries 要在jdbc配置allowMultiQueries=true，可以以分号为分隔符一次性执行多条sql。
   * @param isAutoCommit 在开启allowMultiQueries后开事务，批量执行update性能可以提升100倍。
   * */
  public static int executeBatchUpdate(Connection conn,List<String> subUpdateSqlList,boolean allowMultiQueries,boolean isAutoCommit) throws SQLException{
    boolean notEqual=conn.getAutoCommit()!=isAutoCommit;
    if(notEqual)
      conn.setAutoCommit(isAutoCommit);
//    TimeMeter tm1=new TimeMeter();
//    esdk.sout("正在Update{}条记录",subUpdateSqlList.size());
    boolean execSuccess;
    int subTotal=0;
    try(Statement stat=conn.createStatement()){
      if(allowMultiQueries){
        String multiQueries=esdk.str.listToStr(subUpdateSqlList,"\n");
        execSuccess=stat.execute(multiQueries);
        subTotal=stat.getUpdateCount();
      }else{
        for(String sql: subUpdateSqlList){
          if(esdk.str.isValid(sql)){
            stat.addBatch(esdk.str.addEnd(sql,";"));
          }
        }
        subTotal=stat.executeBatch().length;
      }
//      SQLAssistant.printSql(esdk.str.format("执行{}条update语句，耗时{},SQL：{}...",subTotal,tm1.getHumanElapse(),esdk.str.addEnd(subUpdateSqlList.get(0),";")));
      if(!conn.getAutoCommit())
        conn.commit();
      if(notEqual)
        conn.setAutoCommit(!conn.getAutoCommit());
      stat.closeOnCompletion();
    }
    return subTotal;
  }

  public static ABResultSet getResultSet(Statement stat,String sql) throws SQLException{
    try{
      ABResultSet result=new ABResultSet(stat.executeQuery(sql));
      stat.closeOnCompletion();
      return result;
    }catch(Exception e){
      esdk.sql.close(stat);
      throw new SQLException(e+" SQL语句："+sql);
    }
  }

  public static ABResultSet getResultSet(Connection conn,String sql) throws SQLException{
    Statement statement=conn.createStatement();
    try{
      ABResultSet result=new ABResultSet(statement.executeQuery(sql));
      statement.closeOnCompletion();
      return result;
    }catch(Exception e){
      esdk.sql.close(statement);
      throw new SQLException(e+" SQL语句："+sql);
    }
  }

  public static IRowSet getRowSet(Connection conn,String sql) throws SQLException{
    return new ABRowSet(getResultSet(conn,sql));
  }

  public static boolean execute(Connection conn,String sql) throws SQLException{
    try(Statement stat=conn.createStatement()){
      return stat.execute(sql);
    }catch(SQLException e){
      throw new SQLException(e+" SQL语句："+sql);
    }
  }

  public static boolean execute(Statement stat,String sql) throws SQLException{
    try{
      return stat.execute(sql);
    }catch(SQLException e){
      throw new SQLException(e+" SQL语句："+sql);
    }
  }

  private static void test() throws ParseException{
    esdk.tool.assertEquals(EasySql.format("select * from member where member_id={0} and code={1} and valid={2} or create_time={3} or name in ({4})"
        ,12,"M0002",true,EasyTime.valueOf("2010-10-23"),new Object[]{"张三","李四"}),
      "select * from member where member_id=12 and code='M0002' and valid=1 or create_time='2010-10-23' or name in ('张三','李四')");
    HashMap params=new HashMap();
    params.put("memberId",12);
    params.put("code","M0002");
    params.put("valid",true);
    params.put("createTime",EasyTime.valueOf("2010-10-23"));
    params.put("names",new Object[]{"张三","李四"});
    esdk.tool.assertEquals(EasySql.format("select * from member where member_id=${memberId} and code=${code} and valid=${valid} or create_time=${createTime} or name in (${names})",params)
      ,"select * from member where member_id=12 and code='M0002' and valid=1 or create_time='2010-10-23' or name in ('张三','李四')");
    resetPrimaryKey(null);
    esdk.sql.genPrimaryKey(null);
    int seq=esdk.sql.genPrimaryKey("");
    esdk.tool.assertEquals(seq,10000002);
    Long snowId=genMySnowFlakeId();
    esdk.sout("自定义雪花ID：",snowId,"，长度：",snowId.toString().length());
    esdk.sout("自定义IntID：",genIntId());
    Long hutoolSnowFlakeId=new Snowflake().nextId();
    esdk.sout("hutool_snowflake_id:",hutoolSnowFlakeId,",length:",hutoolSnowFlakeId.toString().length());
    esdk.sout(esdk.time.toLocalDateTime(new Date(new Snowflake().getGenerateDateTime(1595604084641157776L))));
    esdk.sout(esdk.time.toLocalDateTime(new Date(new Snowflake().getGenerateDateTime(1339648633683024799L))));

  }

  public static void main(String[] args) throws Exception{
    test();
    //testOld();
  }

}
